Attribute Module_Name = "CellHandle"
/*******************************|
|			  工具箱				|
|______________________________*/

/** 入口 */
function WPS超级工具箱V1_0() {
	
	// 宏
	Main_UserForm.CommandButton1.Caption = "合并重复项";
	Main_UserForm.CommandButton2.Caption = "正则替换";
	Main_UserForm.CommandButton3.Caption = "去除前后空白字符";
	Main_UserForm.CommandButton4.Caption = "向下填充内容";
	Main_UserForm.CommandButton5.Caption = "向上填充内容";
	Main_UserForm.CommandButton6.Caption = "路径转图片";
	Main_UserForm.CommandButton7.Caption = "高亮满足条件的单元格";
	Main_UserForm.CommandButton8.Caption = "科学计数还原";
	
	// 自定义函数
	Main_UserForm.CommandButton9.Caption = "拆分字符串";
	Main_UserForm.CommandButton10.Caption = "合并区域内容";
	Main_UserForm.CommandButton11.Caption = "提取正则匹配的内容";
	Main_UserForm.CommandButton12.Caption = "提取数字";
	Main_UserForm.CommandButton13.Caption = "提取索引处的数字";
	Main_UserForm.CommandButton14.Caption = "自定义VLookup";
	Main_UserForm.CommandButton15.Caption = "获取选中区域的地址";
	Main_UserForm.CommandButton16.Caption = "敬请期待...";
	
	Main_UserForm.TextEdit2.Value = "帮助文档：https://exquisite-licorice-fd63fc.netlify.app/#/article/2023/05/30/wps_macros";
	
	Main_UserForm.StartUpPostion = 1;
	Main_UserForm.Show();
}

/** 监听器 */
function Main_UserForm_CommandButton1_Click() {
	Main_UserForm.Hide();
	__mergeSimilarRows();
}
function Main_UserForm_CommandButton2_Click() {
	Main_UserForm.Hide();
	__regReplace();
}
function Main_UserForm_CommandButton3_Click() {
	Main_UserForm.Hide();
	__trim();
}
function Main_UserForm_CommandButton4_Click() {
	Main_UserForm.Hide();
	__fillDown();
}
function Main_UserForm_CommandButton5_Click() {
	Main_UserForm.Hide();
	__fillUp();
}
function Main_UserForm_CommandButton6_Click() {
	Main_UserForm.Hide();
	__insertImageFromSrc();
}
function Main_UserForm_CommandButton7_Click() {
	Main_UserForm.Hide();
	__highLightCellByReg();
}
function Main_UserForm_CommandButton8_Click() {
	Main_UserForm.Hide();
	__scientificCountRestore();
}
function Main_UserForm_CommandButton9_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_Split()";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton10_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_MergeContent(\",\",fn_Addr())";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton11_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_GetMatch()";	
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton12_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_GetNumber()";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton13_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_GetNumberByIndex()";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton14_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_CustomVLookup()";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton15_Click() {
	const effectArea = __getEffectArea();
	effectArea.Cells.Item(1).Value2 = "=fn_Addr()";
	Main_UserForm.Hide();
}
function Main_UserForm_CommandButton16_Click() {
	Main_UserForm.Hide();
}


/*******************************|
|			工具函数				|
|______________________________*/

/**
比较字符串大小 
*/
function __strCompare(getMax, ...strs) {
	let max_str = undefined;
	let min_str = undefined;
	let max_num = 0;
	let min_num = 0;
	strs.forEach(str => {
		let sum = 0;
		for (let i = 0; i < str.length; i++) {
			sum += str.charCodeAt(i);
		}
		if (sum > max_num) {
			max_str = str;
			max_num = sum;
		}
		if (sum < min_num || min_num == 0) {
			min_str = str;
			min_num = sum;
		}
	});
	return getMax ? max_str : min_str;
}
/**
获取有效区域
*/
function __getEffectArea(range) {
	let has_error = false;
	const selection = range ? range : Application.Selection;
	const worksheet =  selection.Worksheet;
	let effectArea;
	const sel_addr_spl = selection.Address().split(":");
	if (sel_addr_spl.length == 2) {
		const ch_re = /[a-zA-Z]+/g;
		const num_re = /[0-9]+/g;
		const used = worksheet.UsedRange;
		if (used.Cells.Count === 1) {
			effectArea = used;
		} 
		else {
			const used_addr_spl = used.Address().split(":");
			const used_addr_start = used_addr_spl[0];
			const used_addr_end = used_addr_spl[1];
			const sel_addr_start = sel_addr_spl[0];
			const sel_addr_end = sel_addr_spl[1];
			let eff_start_col, eff_end_col, eff_start_row, eff_end_row;
			if (/^\$[a-zA-Z]+$/.test(sel_addr_start)) {
				const sel_start_col = sel_addr_start.match(ch_re)[0];
				const sel_end_col = sel_addr_end.match(ch_re)[0];
				const used_start_col = used_addr_start.match(ch_re)[0];
				const used_end_col = used_addr_end.match(ch_re)[0];
				
				has_error = (sel_start_col != used_end_col && sel_end_col != used_start_col) && (__strCompare(true, sel_start_col, used_end_col) == sel_start_col || __strCompare(false, sel_end_col, used_start_col) == sel_end_col);
				
				eff_start_col = __strCompare(true, sel_start_col, used_start_col);
				eff_end_col = __strCompare(false, sel_end_col, used_end_col);
				eff_start_row = Number.parseInt(used_addr_start.match(num_re)[0])
				eff_end_row = Number.parseInt(used_addr_end.match(num_re)[0])
			}
			else if (/^\$[0-9]+$/.test(sel_addr_start)) {
				const sel_start_row = Number.parseInt(sel_addr_start.match(num_re)[0]);
				const sel_end_row = Number.parseInt(sel_addr_end.match(num_re)[0]);
				const used_start_row = Number.parseInt(used_addr_start.match(num_re)[0]);
				const used_end_row = Number.parseInt(used_addr_end.match(num_re)[0]);
				
				has_error = sel_start_row > used_end_row || sel_end_row < used_start_row;
				
				eff_start_row = Math.max(sel_start_row, used_start_row);
				eff_end_row = Math.min(sel_end_row, used_end_row);
				eff_start_col = used_addr_start.match(ch_re)[0];
				eff_end_col = used_addr_end.match(ch_re)[0];
			}
			else {
				const sel_start_row = Number.parseInt(sel_addr_start.match(num_re)[0]);
				const sel_end_row = Number.parseInt(sel_addr_end.match(num_re)[0]);
				const sel_start_col = sel_addr_start.match(ch_re)[0];
				const sel_end_col = sel_addr_end.match(ch_re)[0];
				
				const used_start_col = used_addr_start.match(ch_re)[0];
				const used_end_col = used_addr_end.match(ch_re)[0];
				const used_start_row = Number.parseInt(used_addr_start.match(num_re)[0]);
				const used_end_row = Number.parseInt(used_addr_end.match(num_re)[0]);
				
				has_error = (sel_start_col != used_end_col && sel_end_col != used_start_col) && (__strCompare(true, sel_start_col, used_end_col) == sel_start_col || __strCompare(false, sel_end_col, used_start_col) == sel_end_col) || (sel_start_row > used_end_row || sel_end_row < used_start_row);
				
				eff_start_row = Math.max(sel_start_row, used_start_row);
				eff_end_row = Math.min(sel_end_row, used_end_row);
				eff_start_col = __strCompare(true, sel_start_col, used_start_col);
				eff_end_col = __strCompare(false, sel_end_col, used_end_col);
			}
			const addr = "$"+eff_start_col+"$"+eff_start_row+":"+"$"+eff_end_col+"$"+eff_end_row;
			effectArea = worksheet.Range(addr);
		}
	} else {
		effectArea = selection;
	}
	if (has_error) {
		alert("选中区域为空！");
		return;
	}
	return effectArea;
}
/** 创建新工作表 */
function __createSheet() {
	return Application.ActiveWorkbook.Worksheets.Add({After: Application.ActiveWorkbook.ActiveSheet});	
}


/*******************************|
|			 功能				|
|______________________________*/

/** 去除前后空白字符*/
// <- start
function __trim() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		let cell = effectArea.Cells.Item(i);
		cell.NumberFormat = "@";
		cell.Value2 = cell.Text.trim();
	}
}
// <- end

/** 替换选中区域中的指定内容*/
// <- start
function __regReplace() {
	Replace_UserForm.TextEdit1.Value = "";
	Replace_UserForm.TextEdit3.Value = "";
	Replace_UserForm.StartUpPostion = 1;
	Replace_UserForm.Show();
}
function Replace_UserForm_CommandButton1_Click() {
	__replace(Replace_UserForm.TextEdit1.Value);
	Replace_UserForm.Hide();
}
function Replace_UserForm_CommandButton3_Click() {
	__nreplace(Replace_UserForm.TextEdit1.Value);
	Replace_UserForm.Hide();
}
function __replace(re) {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	const keyMap = {
		"{ENTER}": "\n",
		"{TAB}": "\t"
	};
	let replaceTo = Replace_UserForm.TextEdit3.Value;
	replaceTo = keyMap[replaceTo] || replaceTo;
	re = new RegExp(re, "g");
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		let cell = effectArea.Cells.Item(i);
		cell.NumberFormat = "@";
		cell.Value2 = cell.Text.trim().replace(re, replaceTo);
	}
}
function __nreplace(re) {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	const keyMap = {
		"{ENTER}": "\n",
		"{TAB}": "\t"
	};
	let seperator = Replace_UserForm.TextEdit3.Value;
	seperator = keyMap[seperator] || seperator;
	re = new RegExp(re, "g");
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		let cell = effectArea.Cells.Item(i);
		cell.NumberFormat = "@";
		const arr = cell.Text.trim().match(re);
		cell.Value2 = arr && arr.length > 0 ? arr.join(seperator) : "";
	}
}
// <- end


/** 填充单元格内容 */
// <- start
function __fillDown() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	let tmp = [];
	for (let i = effectArea.Cells.Count; i > 0; i--) {
		const cell = effectArea.Cells.Item(i);
		if (!cell.Text) {
			tmp.push(cell.Address());
		} else {
			for (let j = 0; j < tmp.length; j++)
				effectArea.Worksheet.Range(tmp[j]).Value2 = cell.Text;
			tmp = [];
		}
	}
}
// <- end

/** 向上填充内容 */
// <- start
function __fillUp() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	let tmp = [];
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		if (!cell.Text) {
			tmp.push(cell.Address());
		} else {
			for (let j = 0; j < tmp.length; j++)
				effectArea.Worksheet.Range(tmp[j]).Value2 = cell.Text;
			tmp = [];
		}
	}
}
// <- end


function 拆分单元格() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	let arr = [];
	for (let i = 1; i < effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		arr.push(cell.Text.split("\n"))
	}
	arr = arr.flat();
	const sheet = __createSheet();
	for (let i = 0; i < arr.length; i++) {
		const cell = sheet.Rows.Item(i+1).Cells.Item(1);
		cell.NumberFormat = "@";
		cell.Value2 = arr[i]
	}
}

function 扁平化行() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	const worksheet = effectArea.Worksheet;
	const arr = [];
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		arr.push(cell.Text.split(/\s+/))
	}
	const _arr = arr.flat();
	const sheet = __createSheet();
	for (let i = 0; i < _arr.length; i++) {
		const range = sheet.Range("A"+(i+1));
		range.NumberFormat = "@";
		range.Value2 = _arr[i];
	}
}

function __scientificCountRestore() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		cell.NumberFormat = "@"
		cell.Value2 = cell.Formula;
	}
}

function 合并列内容() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	for (let i = 1; i <= effectArea.Rows.Count; i++) {
		const row = effectArea.Rows.Item(i);
		let str = "";
		for (let j = 1; j <= row.Cells.Count; j++) {
			str += row.Cells.Item(j).Text;
		}
		row.Cells.Item(1).Value2 = str;
	}
}

/** 单元格路径转图片 */
// <- start
function __insertImageFromSrc() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	const worksheet = effectArea.Worksheet;
	effectArea.RowHeight = 75;
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		const src = cell.Text;
		const picture = worksheet.Pictures().Insert(src);
		const w = picture.Width;
		const h = picture.Height;
		const ratio = w / h;
		picture.Height = 60;
		picture.Width = 60 * ratio;
		picture.Left = cell.Left + cell.Width / 2 - picture.Width / 2;
		picture.Top = cell.Top + cell.Height / 2 - picture.Height / 2;
		picture.placement = xlMoveAndSize;
	}
}
// <- end

/** 按列合并重复项 */
// <- start
function __mergeSimilarRows()
{
	MergeSimilarRows_UserFom.StartUpPostion = 1;
	MergeSimilarRows_UserFom.Show();
}
function __ExcuteMerge() {
	const effectArea = __getEffectArea();
	if (!effectArea) return;
	
	let mergeIndexs = MergeSimilarRows_UserFom.TextEdit1.Value.match(/\d+/g);
	let mergeSymbol = MergeSimilarRows_UserFom.TextEdit2.Value;
	let onlyLast = MergeSimilarRows_UserFom.CheckBox1.Value;
	
	mergeIndexs = mergeIndexs ? mergeIndexs.map(index => Number.parseInt(index)) : [];
	
	const rows = effectArea.Rows;
	
	const set = {};
	
	for (let i = 1; i <= rows.Count; i++) {
		let key = "";
		const cells = rows.Item(i).Cells;
		const cellData = {};
		for (let j = 1; j <= cells.Count; j++) {
			const cell = cells.Item(j);
			if (mergeIndexs.includes(j)) {
				if (cellData.hasOwnProperty(j)) {
					cellData[j].push(cell.Text);
				} else {
					cellData[j] = [cell.Text];
				}
			} else {
				key += cell.Text;
				cellData[j] = cell.Text;
			}
		}
		if (set.hasOwnProperty(key)) {
			for (let k of mergeIndexs) {
				set[key][k].push(cellData[k][0]);	
			}
		} else {
			set[key] = cellData;
		}
	}
	
	const sheet = __createSheet();
	
	const rowKeys = Object.keys(set);
	
	for (let i = 0; i < rowKeys.length; i++) {
		const cellData = set[rowKeys[i]];
		const colKeys = Object.keys(cellData);
		for (let j = 0; j < colKeys.length; j++) {
			let cellText = cellData[colKeys[j]];
			let _cellText = '';
			if (typeof cellText === "object") {
				if (onlyLast) {
					_cellText = cellText[cellText.length - 1];
				} else {
					for (let k = 0; k < cellText.length; k++) {
						if (k != cellText.length - 1) _cellText += cellText[k] + mergeSymbol;
						else _cellText +=  cellText[k]
					}
				}
			} else {
				_cellText += cellText;
			}
			let targetCell = sheet.Rows.Item(i+1).Cells.Item(j+1);
			targetCell.NumberFormat = "@";
			targetCell.Value2 = _cellText;
		}
	}
	MergeSimilarRows_UserFom.Hide();
}
function MergeSimilarRows_UserFom_Change()
{
	MergeSimilarRows_UserFom.Hide();
}
function MergeSimilarRows_UserFom_CommandButton1_Click()
{
	__ExcuteMerge();
}
// <- end __mergeSimilarRows

/*
高亮满足条件的单元格
*/
// <- start
function __highLightCellByReg() {
	// 显示在屏幕中心
	HighlightCell_UserForm.StartUpPostion = 1;
	HighlightCell_UserForm.Show();
}
function highLightCell() {
	const effectArea = __getEffectArea();
	const re = new RegExp(HighlightCell_UserForm.TextEdit1.Value);
	for (let i = 1; i <= effectArea.Cells.Count; i++) {
		const cell = effectArea.Cells.Item(i);
		if (re.test(cell.Text)) {
			cell.Interior.Color = 255*3;
		}
	}
	HighlightCell_UserForm.Hide();
}
function HighlightCell_UserForm_Change() {
	HighlightCell_UserForm.Hide();
}
function HighlightCell_UserForm_CommandButton2_Click() {
	highLightCell();
}
// <- end

/*******************************|
|			自定义函数			|
|______________________________*/

/**
根据符号拆分字符串
*/
function fn_Split(单元格, 分隔符, 索引) {
	let range = 单元格;
	let sep = 分隔符;
	let index = 索引;
	if (!range || range.Rows.Count > 1 || range.Rows.Item(1).Cells.Count > 1) return "#ARGS?";
	if (!sep) sep = ",";
	if (!index) index = "0";
	return range.Text.split(sep)[index];
}

/**
获取区域地址
*/
function fn_Addr(选中区域) {
	let range = 选中区域;
	if (!range) return "#ARGS?"
	return range.Address();
}

/**
合并区域内容
*/
function fn_MergeContent(连接符, ...单元格) {
	const ranges = 单元格;
	let linkSymbol = 连接符;
	if (ranges.length == 0) return "#ARGS?";
	if (!linkSymbol) linkSymbol = "";
	let result = [];
	let re = /^\$[a-zA-Z]+$/;
	for (let range of ranges) {
		let effectArea = Application.ActiveSheet.Range(range);
		const address = Application.ActiveSheet.Range(range).Address();
		if (address.includes(":")) {
			const addrs = address.split(":");
			const startAddr = addrs[0].trim();
			const endAddr = addrs[1].trim();
			if (re.test(startAddr) && re.test(endAddr)) {
				const usedRange = effectArea.Worksheet.UsedRange;
				const rowCount = usedRange.Rows.Count;
				effectArea = usedRange.Range(startAddr+"1:"+endAddr+rowCount);
			}
		}
		for (let i = 1; i <= effectArea.Cells.Count; i++) {
			const cell = effectArea.Cells.Item(i);
			result.push(cell.Text);
		}
	}
	return result.join(linkSymbol);
}

/**
根据正则表达式提取匹配的内容
*/
function fn_GetMatch(单元格, 正则表达式, 匹配模式, 分隔符) {
	let range = 单元格;
	let re = 正则表达式;
	let mode = 匹配模式;
	let sep = 分隔符;
	if (!range || range.Rows.Count > 1 || range.Rows.Item(1).Cells.Count > 1) return "#ARGS?";
	if (!re) return "#ARGS?";
	if (!mode) mode = "g";
	if (!sep) sep = "";
	return range.Text.match(new RegExp(re, mode)).join(sep);
}

/**
提取单元格中的数字
*/
function fn_GetNumber(单元格, 分隔符) {
	let range = 单元格;
	let sep = 分隔符;
	if (!range || range.Rows.Count > 1 || range.Rows.Item(1).Cells.Count > 1) return "#ARGS?";
	if (!sep) sep = "";
	return range.Text.match(/\d+/g).join(sep);
}

/**
提取单元格中处在索引处的数字
*/
function fn_GetNumberByIndex(单元格, 索引) {
	let range = 单元格;
	let index = 索引;
	if (!range || range.Rows.Count > 1 || range.Rows.Item(1).Cells.Count > 1) return "#ARGS?";
	if (!index) index = "0";
	return range.Text.match(/\d+/g)[index];
}

function fn_CustomVLookup(目标, 源, 查找列, 返回列) {
	const target = __getEffectArea(目标);
	const source = __getEffectArea(源);
	const findNo = 查找列;
	const returnNo = 返回列;
	let targetCell = target.Cells.Item(1);
	let sourceCol = source.Columns.Item(findNo);
	let sourceColCells = sourceCol.Cells;
	let returnCol = source.Columns.Item(returnNo);
	for (let i = 1; i <= sourceColCells.Count; i++) {
		const targetStr = targetCell.Text;
		const sourceColCell =sourceColCells.Item(i);
		if (sourceColCell.Text.includes(targetStr)) {
			return returnCol.Cells.Item(i).Text;
		}
	}
	return "#NotFound";
}

